"""SKU import from Excel files."""

from __future__ import annotations

import json
from typing import Dict, Optional, Tuple

import frappe
from frappe import _, _dict

from frappe.utils import get_datetime, now_datetime, cint, getdate, flt
from frappe.defaults import get_global_default

from ..utils.shopify_client import ShopifyClient, ShopifyClientError


class MissingSKUError(Exception):
    """Raised when an order references Item codes that are not available in ERP."""


def _ensure_item_group_exists(product_category: str, parent_group: str = "All Item Groups", spu_code: str = None, product_name: str = None) -> str:
    """
    确保 Item Group 存在，如果不存在则创建
    使用SPU作为Item Group ID，Product Category作为Item Group Name
    """
    if not product_category:
        return parent_group
    
    # 如果提供了SPU代码，使用SPU+Product Category作为Item Group名称
    if spu_code:
        item_group_name = f"{spu_code} - {product_category}"  # 使用SPU - Product Category格式
    else:
        # 否则使用商品品类作为名称
        item_group_name = product_category
    
    # 检查 Item Group 是否已存在
    existing_group = frappe.db.get_value("Item Group", {"name": item_group_name})
    if existing_group:
        return existing_group
    
    try:
        # 创建新的 Item Group
        item_group_doc = frappe.new_doc("Item Group")
        item_group_doc.item_group_name = item_group_name  # 使用SPU - Product Category格式
        item_group_doc.parent_item_group = parent_group
        item_group_doc.is_group = 0
        
        item_group_doc.insert(ignore_permissions=True)
        
        return item_group_name
        
    except Exception as e:
        frappe.logger().error(f"[Item Group创建] 创建失败: {item_group_name}, 错误: {str(e)}")
        # 如果创建失败，返回父组
        return parent_group


def _ensure_unit_exists(unit_name: str) -> bool:
    """
    确保UOM单位存在，如果不存在则创建
    """
    if not unit_name:
        return False
    
    # 检查UOM是否已存在
    existing_uom = frappe.db.get_value("UOM", {"uom_name": unit_name})
    if existing_uom:
        return True
    
    try:
        # 创建新的UOM
        uom_doc = frappe.new_doc("UOM")
        uom_doc.uom_name = unit_name
        uom_doc.must_be_whole_number = 1  # 默认为整数
        uom_doc.insert(ignore_permissions=True)
        
        frappe.logger().info(f"[SKU映射] 创建新的UOM单位: {unit_name}")
        return True
        
    except Exception as e:
        frappe.logger().error(f"[SKU映射] 创建UOM单位失败: {unit_name}, 错误: {str(e)}")
        return False


@frappe.whitelist()
def generate_standard_names(sku_code: str, variant_title: str, base_chinese_name: str) -> str:
    """
    根据SKU、变体标题和基础中文名称生成标准中文名称
    格式：产品名称+颜色+规格
    """
    if not base_chinese_name or not variant_title:
        return base_chinese_name
    
    # 颜色映射
    color_mapping = {
        "black": "黑色", "blk": "黑色", "blkl": "黑色", "blkm": "黑色", "blks": "黑色",
        "white": "白色", "wht": "白色", "snow": "白色", "snol": "白色", "snom": "白色", "snos": "白色",
        "blue": "蓝色", "blu": "蓝色", "sto": "蓝色", "stormy": "蓝色",
        "stone": "石色", "stn": "石色", "stnl": "石色", "stnm": "石色", "stns": "石色",
        "gray": "灰色", "grey": "灰色", "gry": "灰色",
        "red": "红色", "rd": "红色",
        "green": "绿色", "grn": "绿色",
        "brown": "棕色", "brn": "棕色",
        "pink": "粉色", "pnk": "粉色",
        "purple": "紫色", "prp": "紫色",
        "yellow": "黄色", "ylw": "黄色",
        "orange": "橙色", "org": "橙色"
    }
    
    # 规格映射
    size_mapping = {
        "s": "S号", "small": "S号",
        "m": "M号", "medium": "M号", 
        "l": "L号", "large": "L号",
        "xl": "XL号", "x-large": "XL号",
        "xxl": "XXL号", "xx-large": "XXL号",
        "xs": "XS号", "x-small": "XS号"
    }
    
    # 解析变体标题，格式通常是 "颜色 / 规格" 或 "颜色 / 规格"
    variant_parts = variant_title.split(" / ")
    if len(variant_parts) != 2:
        return base_chinese_name
    
    color_part = variant_parts[0].lower().strip()
    size_part = variant_parts[1].lower().strip()
    
    # 查找颜色
    chinese_color = ""
    for key, value in color_mapping.items():
        if key in color_part:
            chinese_color = value
            break
    
    # 查找规格
    chinese_size = ""
    for key, value in size_mapping.items():
        if key in size_part:
            chinese_size = value
            break
    
    # 组合最终名称
    result = base_chinese_name
    if chinese_color:
        result += chinese_color
    if chinese_size:
        result += chinese_size
    
    return result


def load_sku_base_data():
    """
    从 01_sku_base.ini 文件动态加载SKU基础数据
    返回: dict {sku: {chinese_name, unit, weight, declared_value}}
    """
    sku_mapping = {}
    try:
        import os
        base_path = os.path.join(frappe.get_app_path("upsystem"), "utils", "tariff", "01_sku_base.ini")
        
        with open(base_path, 'r', encoding='utf-8') as f:
            lines = f.readlines()[1:]  # 跳过标题行
            for line in lines:
                line = line.strip()
                if not line:
                    continue
                    
                parts = line.split('\t')
                if len(parts) >= 9:
                    sku = parts[4].strip()
                    spu = parts[0].strip()  # SPU
                    product_name = parts[1].strip() + " " + sku # Product Name
                    base_chinese_name = parts[5].strip()  # 产品中文名
                    variant_name = parts[6].strip()  # 变体名称
                    chinese_name = process_chinese_name_dynamic(sku, base_chinese_name, variant_name)  # 直接处理中文名称
                    unit = parts[3].strip()
                    product_category = parts[2].strip()  # 商品品类
                    weight_str = parts[7].strip()
                    declared_value_str = parts[8].strip()
                    
                    # 解析重量，移除$符号
                    try:
                        weight = float(weight_str.replace('$', '')) if weight_str else 0.1
                    except (ValueError, TypeError):
                        weight = 0.1
                    
                    # 解析申报价值，移除$符号
                    try:
                        declared_value = float(declared_value_str.replace('$', '')) if declared_value_str else 0.0
                    except (ValueError, TypeError):
                        declared_value = 0.0
                    
                    # 存储完整的SKU映射（不是前缀）
                    if sku:
                        sku_mapping[sku] = {
                            "spu": spu,
                            "product_name": product_name,
                            "chinese_name": chinese_name,  # 已经是最终处理过的中文名称
                            "unit": unit,
                            "product_category": product_category,
                            "weight": weight,
                            "declared_value": declared_value
                        }
        
        frappe.logger().info(f"[SKU基础数据] 成功加载 {len(sku_mapping)} 个SKU映射")
        return sku_mapping
        
    except Exception as e:
        frappe.logger().error(f"[SKU基础数据] 加载失败: {str(e)}")
        return {}


def load_hs_code_data():
    """
    从 02_sku_hs 文件加载HS编码数据
    返回: dict {sku: hs_code}
    """
    hs_mapping = {}
    try:
        import os
        hs_path = os.path.join(frappe.get_app_path("upsystem"), "utils", "tariff", "02_sku_hs")
        
        with open(hs_path, 'r', encoding='utf-8') as f:
            lines = f.readlines()[1:]  # 跳过标题行
            for line in lines:
                line = line.strip()
                if not line:
                    continue
                    
                parts = line.split('\t')
                if len(parts) >= 2:
                    sku = parts[0].strip()
                    hs_code = parts[1].strip()
                    if sku and hs_code:
                        hs_mapping[sku] = hs_code
        
        frappe.logger().info(f"[HS编码数据] 成功加载 {len(hs_mapping)} 个SKU的HS编码")
        return hs_mapping
        
    except Exception as e:
        frappe.logger().error(f"[HS编码数据] 加载失败: {str(e)}")
        return {}




def get_hs_code(sku_code: str) -> str:
    """
    根据SKU代码获取HS编码
    """
    if not sku_code:
        return ""
    
    # 动态加载HS编码数据
    hs_mapping = load_hs_code_data()
    
    return hs_mapping.get(sku_code, "")


def get_sku_info(sku_code: str) -> dict:
    """
    获取SKU的完整信息，包括SPU、产品名称、中文名称、单位、商品品类、重量、申报价值
    """
    if not sku_code:
        return {
            "spu": "",
            "product_name": "",
            "chinese_name": "",
            "unit": "PCS",
            "product_category": "",
            "weight": 0.10,
            "declared_value": 0.0
        }
    
    # 动态加载SKU基础数据
    sku_mapping = load_sku_base_data()
    
    # 直接匹配完整SKU
    if sku_code in sku_mapping:
        return sku_mapping[sku_code].copy()
    
    # 如果没有找到匹配，返回默认值
    return {
        "spu": "",
        "product_name": "",
        "chinese_name": "",
        "unit": "PCS",
        "product_category": "",
        "weight": 0.10,
        "declared_value": 0.0
    }


def get_valid_sku_list() -> set:
    """
    获取所有有效的SKU列表（从01_sku_base.ini中提取）
    """
    try:
        # 直接使用load_sku_base_data函数获取SKU列表
        sku_mapping = load_sku_base_data()
        valid_skus = set(sku_mapping.keys())
        
        frappe.logger().info(f"[有效SKU列表] 成功加载 {len(valid_skus)} 个有效SKU")
        return valid_skus
        
    except Exception as e:
        frappe.logger().error(f"[有效SKU列表] 加载失败: {str(e)}")
        return set()




def get_shopify_images_batch(shop_name: str, sku_list: list) -> dict:
    """
    批量从Shopify获取SKU图片URL
    使用现有的 fetch_skus 方法，支持大量SKU的批量查询
    """
    try:
        # 获取Shopify客户端
        client = ShopifyClient(shop_name)
        
        # 将SKU列表转换为查询条件
        sku_query = " OR ".join([f"sku:{sku}" for sku in sku_list])
        
        image_mapping = {}
        has_next = True
        cursor = None
        
        while has_next:
            try:
                # 使用现有的 fetch_skus 方法
                skus, next_cursor, has_next_page = client.fetch_skus(
                    first=100,  # 每页最多100个产品
                    after=cursor,
                    query=sku_query,
                )
                
                if not skus:
                    break
                
                # 处理每个SKU数据
                for entry in skus:
                    product = entry.get("product", {})
                    variant = entry.get("variant", {})
                    
                    sku = variant.get("sku", "")
                    
                    if not sku or sku not in sku_list:
                        continue
                    
                    # 获取图片URL
                    featured_image = product.get("featuredImage")
                    image_url = featured_image.get("originalSrc") if featured_image else None
                    
                    if image_url:
                        image_mapping[sku] = image_url
                
                # 更新分页信息
                cursor = next_cursor
                has_next = has_next_page
                
            except Exception as e:
                frappe.logger().error(f"[Shopify图片获取] 查询失败: {str(e)}")
                break
        
        return image_mapping
        
    except Exception as e:
        frappe.logger().error(f"[Shopify图片获取] 失败: {str(e)}")
        return {}


def process_chinese_name_dynamic(sku_code: str, base_chinese_name: str, variant_title: str = "") -> str:
    """
    动态处理中文名称
    根据SKU代码和变体标题生成完整的中文名称
    """
    if not base_chinese_name:
        return ""
    
    if not variant_title:
        return base_chinese_name
    
    # 颜色映射
    color_mapping = {
        "black": "黑色", "blk": "黑色", "blkl": "黑色", "blkm": "黑色", "blks": "黑色",
        "white": "白色", "wht": "白色", "snow": "白色", "snol": "白色", "snom": "白色", "snos": "白色",
        "blue": "蓝色", "blu": "蓝色", "sto": "蓝色", "stormy": "蓝色",
        "stone": "石色", "stn": "石色", "stnl": "石色", "stnm": "石色", "stns": "石色",
        "gray": "灰色", "grey": "灰色", "gry": "灰色",
        "red": "红色", "rd": "红色",
        "green": "绿色", "grn": "绿色",
        "brown": "棕色", "brn": "棕色",
        "pink": "粉色", "pnk": "粉色",
        "purple": "紫色", "prp": "紫色",
        "yellow": "黄色", "ylw": "黄色",
        "orange": "橙色", "org": "橙色"
    }
    
    # 规格映射
    size_mapping = {
        "s": "S号", "small": "S号",
        "m": "M号", "medium": "M号", 
        "l": "L号", "large": "L号",
        "xl": "XL号", "x-large": "XL号",
        "xxl": "XXL号", "xx-large": "XXL号",
        "xs": "XS号", "x-small": "XS号"
    }
    
    # 解析变体标题，格式通常是 "颜色 / 规格"
    variant_parts = variant_title.split(" / ")
    if len(variant_parts) != 2:
        return base_chinese_name
    
    color_part = variant_parts[0].lower().strip()
    size_part = variant_parts[1].lower().strip()
    
    # 查找颜色
    chinese_color = ""
    for key, value in color_mapping.items():
        if key in color_part:
            chinese_color = value
            break
    
    # 查找规格
    chinese_size = ""
    for key, value in size_mapping.items():
        if key in size_part:
            chinese_size = value
            break
    
    # 组合最终名称
    result = base_chinese_name
    if chinese_color:
        result += chinese_color
    if chinese_size:
        result += chinese_size
    
    return result


@frappe.whitelist()
def import_skus_from_excel_data(
    parent_item_group=None, 
    default_company=None, 
    default_warehouse=None, 
    default_supplier=None,
    shop_name=None
):
    """
    从Excel数据导入SKU到ERPNext
    主循环基于sku_mapping，批量获取图片，直接处理数据
    """
    try:
        frappe.logger().info(f"[Excel SKU导入] 开始导入SKU")
        
        # 加载SKU基础数据
        sku_mapping = load_sku_base_data()
        if not sku_mapping:
            return {
                "success": False,
                "error": "无法加载SKU基础数据，请检查 01_sku_base.ini 文件"
            }
        
        # 验证参数
        if not parent_item_group:
            parent_item_group = "All Item Groups"
        if not default_company:
            default_company = frappe.defaults.get_user_default("Company")
        if not default_warehouse:
            default_warehouse = frappe.defaults.get_user_default("Warehouse")
        if not default_supplier:
            default_supplier = frappe.defaults.get_user_default("Supplier")
        
        if not all([parent_item_group, default_company, default_warehouse, default_supplier]):
            return {
                "success": False,
                "error": "缺少必要参数：parent_item_group, default_company, default_warehouse, default_supplier"
            }
        
        # 加载HS编码数据
        hs_mapping = load_hs_code_data()
        
        # 批量获取图片URL（如果提供了shop_name）
        image_mapping = {}
        if shop_name:
            sku_list = list(sku_mapping.keys())
            image_mapping = get_shopify_images_batch(shop_name, sku_list)
        
        # 初始化计数器
        created = 0
        updated = 0
        errors = []
        
        # 主循环：遍历sku_mapping
        for sku_code, sku_data in sku_mapping.items():
            try:
                # 直接获取基础数据
                spu = sku_data.get("spu", "")
                product_name = sku_data.get("product_name", "")
                chinese_name = sku_data.get("chinese_name", "")  # 已经是最终处理过的中文名称
                unit = sku_data.get("unit", "PCS")
                product_category = sku_data.get("product_category", "")
                weight = sku_data.get("weight", 0.1)
                declared_value = sku_data.get("declared_value", 0.0)
                
                # 获取HS编码
                hs_code = hs_mapping.get(sku_code, "")
                
                # 获取图片URL
                image_url = image_mapping.get(sku_code, "")
                
                # 检查Item是否已存在
                existing_item = frappe.db.get_value("Item", {"item_code": sku_code})
                
                if existing_item:
                    item = frappe.get_doc("Item", existing_item)
                    updated = 1
                    created = 0
                else:
                    item = frappe.new_doc("Item")
                    item.item_code = sku_code
                    created = 1
                    updated = 0
                
                # 确保UOM存在，如果创建失败则使用默认值
                if not _ensure_unit_exists(unit):
                    frappe.logger().warning(f"[UOM创建失败] 使用默认单位 'Nos' 替代 '{unit}'")
                    unit = "Nos"
                
                # 设置基础信息
                item.flags.ignore_permissions = True
                
                # 构建Item英文名称：Product Name + Variant Name
                variant_name = sku_data.get("variant_name", "")
                if variant_name:
                    item.item_name = f"{product_name} {variant_name}"
                else:
                    item.item_name = product_name
                
                item.custom_item_name_cn = chinese_name
                item.stock_uom = unit
                item.weight_per_unit = weight
                item.disabled = 0
                
                # 基础商品信息
                item.is_stock_item = 1
                item.is_sales_item = 1
                item.is_purchase_item = 1
                
                # 设置Item Group
                if product_category and spu and product_name:
                    item_group_name = _ensure_item_group_exists(product_category, parent_item_group, spu, product_name)
                    item.item_group = item_group_name
                elif product_category:
                    item_group_name = _ensure_item_group_exists(product_category, parent_item_group)
                    item.item_group = item_group_name
                else:
                    item.item_group = parent_item_group
                
                # 设置价格
                if declared_value > 0:
                    item.valuation_rate = declared_value 
                    item.custom_sales_priceusd = declared_value 
                
                # 设置图片
                if image_url:
                    item.image = image_url
                
                # 设置HS编码
                if hs_code and hasattr(item, 'custom_hs_code'):
                    item.custom_hs_code = hs_code
                
                # 设置条码
                if not existing_item:
                    if not item.barcodes:
                        item.barcodes = []
                    barcode_row = item.append("barcodes")
                    barcode_row.barcode = sku_code
                
                # 设置供应商信息
                if default_supplier:
                    existing_supplier = False
                    if hasattr(item, 'supplier_items'):
                        for supplier_item in item.supplier_items:
                            if supplier_item.supplier == default_supplier:
                                existing_supplier = True
                                break
                    
                    if not existing_supplier:
                        supplier_item = item.append("supplier_items")
                        supplier_item.supplier = default_supplier
                        supplier_item.supplier_part_no = sku_code
                
                # 保存Item
                item.save()
                
                # 创建Item Defaults
                if default_company and default_warehouse:
                    try:
                        # 删除其他公司的Item Default记录
                        frappe.db.delete("Item Default", {
                            "parent": sku_code,
                            "parentfield": "item_defaults",
                            "parenttype": "Item",
                            "company": ["!=", default_company]
                        })
                        
                        # 检查当前公司的Item Default是否存在
                        existing_default = frappe.db.exists("Item Default", {
                            "parent": sku_code,
                            "parentfield": "item_defaults",
                            "parenttype": "Item",
                            "company": default_company
                        })
                        
                        if not existing_default:
                            item_default = frappe.new_doc("Item Default")
                            item_default.parent = sku_code
                            item_default.parentfield = "item_defaults"
                            item_default.parenttype = "Item"
                            item_default.company = default_company
                            item_default.default_warehouse = default_warehouse
                            item_default.insert(ignore_permissions=True)
                            
                    except Exception as e:
                        frappe.logger().error(f"创建 Item Default 失败: {str(e)}")
                
                frappe.logger().info(f"[Excel SKU导入] SKU {sku_code}: {'创建' if created else '更新'}成功")
                
            except Exception as e:
                error_msg = f"处理SKU失败: {sku_code}, 错误: {str(e)}"
                frappe.logger().error(f"[Excel SKU导入] {error_msg}")
                errors.append(error_msg)
        
        # 构建返回消息
        message = f"Excel SKU导入完成：创建 {created} 个，更新 {updated} 个"
        if errors:
            message += f"，失败 {len(errors)} 个"
        
        return {
            "success": len(errors) == 0,
            "message": message,
            "created": created,
            "updated": updated,
            "errors": errors
        }
        
    except Exception as e:
        frappe.logger().error(f"[Excel SKU导入] 导入失败: {str(e)}")
        return {
            "success": False,
            "error": str(e)
        }


@frappe.whitelist()
def test_sku_data_loading():
    """
    测试SKU数据加载功能
    """
    try:
        # 测试加载SKU基础数据
        sku_mapping = load_sku_base_data()
        frappe.logger().info(f"[测试] 加载了 {len(sku_mapping)} 个SKU的基础数据")
        
        # 测试加载HS编码数据
        hs_mapping = load_hs_code_data()
        frappe.logger().info(f"[测试] 加载了 {len(hs_mapping)} 个SKU的HS编码")
        
        # 测试获取有效SKU列表
        valid_skus = get_valid_sku_list()
        frappe.logger().info(f"[测试] 获取了 {len(valid_skus)} 个有效SKU")
        
        # 测试几个具体的SKU
        test_skus = ["OGTK11-BLKLL", "WCWT22-BLKLL", "MSAS01-BLKL"]
        for sku in test_skus:
            if sku in sku_mapping:
                sku_info = get_sku_info(sku)
                hs_code = get_hs_code(sku)
                frappe.logger().info(f"[测试] SKU {sku}: SPU={sku_info['spu']}, 产品名={sku_info['product_name']}, 中文名={sku_info['chinese_name']}, 单位={sku_info['unit']}, 商品品类={sku_info['product_category']}, 重量={sku_info['weight']}, 申报价值={sku_info['declared_value']}, HS编码={hs_code}")
            else:
                frappe.logger().warning(f"[测试] SKU {sku} 未找到")
        
        return {
            "success": True,
            "message": f"测试完成：加载了 {len(sku_mapping)} 个SKU基础数据，{len(hs_mapping)} 个HS编码，{len(valid_skus)} 个有效SKU"
        }
        
    except Exception as e:
        frappe.logger().error(f"[测试] 失败: {str(e)}")
        return {
            "success": False,
            "error": str(e)
        }















